package tt.dz.biz.dao.account;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.common.BusinessException;
import tt.dz.common.LogUtil;
import tt.dz.vo.AccountDetailVO;
import tt.dz.vo.AccountingRuleVO;
import tt.dz.vo.MerchAccountDividendVO;
import tt.dz.vo.MerchantSubjectAccVO;
import tt.dz.vo.MerchantVO;
import tt.dz.vo.OfflineAccountDetailVO;
import tt.dz.vo.OfflineAccountVO;
import tt.dz.vo.OutOfflineCardMerchant;
import tt.dz.vo.PayChannelVO;
import tt.dz.vo.SubAccountingRuleVO;
import tt.dz.vo.SubjectVO;
import tt.dz.vo.TranInfoVO;
import tt.dz.vo.TranSubVO;
import tt.dz.vo.UserBaseVO;

@Repository("accountingRuleDAO")
public class AccountingDAO
  extends BaseDAO
{
  private static final String QUERY_ACCOUNTING_RULE_BY_SUBSERVICENO = "select  service_no as  serviceNo,sub_service_no as subServiceNo , sub_subject_code as  subSubjectCode, updated_time as  updatedTime, updated_by as  updatedBy, is_valid as  isValid, balance_flag as  balanceFlag,  created_time as  createdTime,rule_seq as ruleSeq,loan_flag as loanFlag,  created_by as  createdBy, subject_code as  subjectCode  from ms_accounting_rule  where is_valid=1 and  service_no=:serviceNo and  sub_service_no=:subServiceNo ";
  private static final String QUERY_TRAN_INFO_WAIT_FOR_ACCOUNTING = "select receiver_balance as  receiverBalance,  trade_no as   tradeNo, service_no as serviceNo,order_type as orderType, abstract_code as  abstractCode, thd_end_time as  thdEndTime, payment_amount as  paymentAmount, receiver_name as  receiverName,receiver_type as receiverType, accounting_date as  accountingDate, plat_time as  platTime, check_account_date as  checkAccountDate, pay_state_code as  payStateCode, payer_type as  payerType, thd_pay_seq as  thdPaySeq,batch_no as batchNo, channel_code as  channelCode, is_divided as  isDivided, receiver as  receiver, response_code as  responseCode, payer as  payer, original_payer as  originalPayer, \toriginal_payer_type as originalPayerType,app_id as  appId,check_account_sate as checkAccountSate, reason as  reason, original_payer_name as  originalPayerName,thd_pay_amount as thdPayAmount, tran_time as  tranTime, cancle_status as  cancleStatus,payer_balance as payerBalance, adjust_trade_no as  adjustTradeNo, account_state as  accountState, has_adjust as  hasAdjust, pay_type_code as  payTypeCode, payer_name as  payerName, thd_poundage as  thdPoundage, order_no as  orderNo, card_type as cardType,card_no as cardNo  from ms_tran_info where account_state=0 order by trade_no desc limit 20 ";
  private static final String QUERY_SUBTRAN_BY_TRANNO = "select  check_account_sate as  checkAccountSate, service_no as  serviceNo, sub_trade_no as  subTradeNo, receiver as  receiver, receiver_type as receiverType, cancle_status as  cancleStatus, payer as  payer,payer_type as payerType,  receiver_bal_type as  receiverBalType,  is_cost as isCost, trade_no as  tradeNo, provider as  provider,receiver_is_self as receiverIsSelf, payment_amount as  paymentAmount, receiver_name as  receiverName   from ms_tran_sub_jrnl  where trade_no=:tradeNo";
  private static final String UPDATE_TRAN_INFO_ACCOUNT_STATE_FOR_STARTACCOUNTING = "update ms_tran_info set account_state=:accountState , start_account_time =:startAccountTime where trade_no=:tradeNo";
  private static final String INSERT_OFFLINE_ACCOUNT_DETAIL = "insert into ms_offline_account_detail(card_no,order_no,trade_no,merchant_code,abstract_code,service_no,thd_pay_seq,pay_flag,accounting_date,plat_time,payment_amount,remark)  values(:cardNo,:orderNo,:tradeNo,:merchantCode,:abstractCode,:serviceNo,:thdPaySeq,:payFlag,:accountingDate,:platTime,:paymentAmount,:remark )";
  private static final String QUERY_PAY_CHANNEL = " select public_key as  publicKey, plat_account as  platAccount, channel_name as  channelName, is_realtime as  isRealtime, remark as  remark, is_valid as  isValid, callback_url as  callbackUrl,  channel_code as   channelCode,   sign_type as    signType from ms_pay_channel where channel_code=:channelCode and is_valid=1";
  public static final String LIST_ALL_ACCOUNTINGRULE = "select * from ms_accounting_rule";
  public static final String QUERY_SUBACCOUNTRULE = "select * from ms_sub_accounting_rule where sub_rule_seq = :subRuleSeq or rule_seq = :ruleSeq ;";
  public static final String INSERT_ACCOUNTINGRULE = "insert into ms_accounting_rule(rule_seq,service_no,sub_service_no,subject_code,sub_subject_code,loan_flag,created_by,created_time,updated_by,updated_time,is_valid,balance_flag)  values(:ruleSeq,:serviceNo,:subServiceNo,:subjectCode,:subSubjectCode,:loanFlag,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid,:balanceFlag )";
  public static final String INSERT_SUBACCOUNTINGRULE = "insert into ms_sub_accounting_rule(sub_rule_seq,rule_seq,subject_code,sub_subject_code,loan_flag,created_by,created_time,updated_by,updated_time,is_valid,balance_flag)  values(:subRuleSeq,:ruleSeq,:subjectCode,:subSubjectCode,:loanFlag,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid,:balanceFlag )";
  public static final String UPDATE_ACCOUNTINGRULE = "update  ms_accounting_rule set service_no = :serviceNo,sub_service_no = :subServiceNo,subject_code = :subjectCode,sub_subject_code = :subSubjectCode,loan_flag = :loanFlag,created_by = :createdBy,created_time = :createdTime,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid,balance_flag = :balanceFlag  where rule_seq = :ruleSeq ";
  public static final String UPDATE_SUBACCOUNTINGRUL = "update  ms_sub_accounting_rule set rule_seq = :ruleSeq,subject_code = :subjectCode,sub_subject_code = :subSubjectCode,loan_flag = :loanFlag,created_by = :createdBy,created_time = :createdTime,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid,balance_flag = :balanceFlag where sub_rule_seq = :subRuleSeq";
  public static final String QUERY_SUBACCOUNTRULE_BY_RULESEQ = "select rule_seq as ruleSeq,sub_rule_seq as subRuleSeq ,subject_code as subjectCode,sub_subject_code as subSubjectCode,loan_flag as loanFlag  from ms_sub_accounting_rule where is_valid=1 and rule_seq = :ruleSeq ";
  private static final String QUERY_COST_MERCHANT_BY_SERVICE_CODE = "SELECT mm.merchant_code as merchantCode,mm.bal_type as balType from ms_cost_service_merchant mc,ms_merchant mm where mc.merchant_code = mm.merchant_code and mm.is_valid = 1 and mc.is_valid=1 and mc.service_no=:serviceNo";
  private static final String QUERY_BANK_ACCOUNT_BY_CHANNEL = " select subject_code as subjectCode,channel_code as channelCode from ms_channel_bankaccount where  channel_code=:channelCode and is_valid=1";
  private static final String QUERY_ASSETS_BY_SERVICENO = "select a.subject_code as subjectCode,a.sub_subject_code as subSubjectCode,a.loan_flag as loanFlag,service_no as serviceNo from ms_assets_lblts_account_rule a , ms_subject s  where a.sub_subject_code = s.subject_code and  a.is_valid=1 and  s.is_valid=1 and  a.service_no=:serviceNo and s.subject_type=:subjectType and a.cprt_bal_type=:cprtBalType";
  private static final String INSERT_MERCH_ACCOUNT_DIVIDEND = "insert into ms_merch_account_dividend(trade_no,merchant_code,subject_code,sub_subject_code,loan_flag,amount,merchant_code1,remark,accounting_date)  values(:tradeNo,:merchantCode,:subjectCode,:subSubjectCode,:loanFlag,:amount,:merchantCode1,:remark,:accountingDate )";
  private static final String INSERT_ACCOUNT_DETAIL = "insert into ms_account_detail(user_id,order_no,trade_no,merchant_code,abstract_code,service_no,pay_type_code,thd_pay_seq,pay_flag,accounting_date,plat_time,payment_amount,remark)  values(:userId,:orderNo,:tradeNo,:merchantCode,:abstractCode,:serviceNo,:payTypeCode,:thdPaySeq,:payFlag,:accountingDate,:platTime,:paymentAmount,:remark )";
  private static final String UPDATE_TRAN_INFO_ACCOUNT_STATE_FOR_ENDACCOUNTING = "update ms_tran_info set account_state=:accountState , end_account_time =:endAccountTime where trade_no=:tradeNo";
  private static final String UPDATE_TMERCHANT_BALANCE = "UPDATE ms_merchant_subject_acc set acct_bal = acct_bal + :acctBal  where merchant_code=:merchantCode and sub_subject_code=:subSubjectCode";
  private static final String QUERY_TRAN_INFO_FOR_MONITOR = "select  trade_no as   tradeNo ,account_state as  accountState, plat_time as platTime  from ms_tran_info where  plat_time >=:platTime and (account_state=0  or account_state = 1 )  ";
  private static final String INSERT_OFFLINE_ACCOUNT = "insert into ms_offline_account(card_no,user_id,card_type,card_bin,area_code,check_code,valid_date,init_date,init_oper,state,open_oper,open_date,remark,card_cost,acct_bal,bat_no,last_date)  values(:cardNo,:userId,:cardType,:cardBin,:areaCode,:checkCode,:validDate,:initDate,:initOper,:state,:openOper,:openDate,:remark,:cardCost,:acctBal,:batNo,:lastDate )";
  private static final String ADD_ENTUSER_BAL = "update ms_user_base set  balance=balance+:balance where user_id =:userId";
  private static final String QUERY_SINGLE_USER_BY_MERCHCODE = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid  from ms_user_base as t  where t.enterprise_code = :merchantCode and t.is_valid = 1 ";
  private static final String QUERY_MERCHANT_BY_CARDTYPE = "select  oo.card_type as cardType ,oo.merchant_code as merchantCode ,mm.short_name as shortName  from  ms_out_offline_card_merchant oo,ms_merchant mm  where oo.merchant_code = mm.merchant_code and oo.card_type=:cardType";
  private static final String QUERY_OFFLINE_ACCOUNT_HIS = "select   a.card_no,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.card_cost,a.acct_bal,a.init_date,a.init_oper,a.state,a.open_oper,a.open_date,a.valid_date,a.last_date,a.remark ,a.bal_is_updated as balIsUpdated  from  ms_offline_account_his a  where  a.card_no=:cardNo";
  private static final String UPDATE_BALUPTAED_FOR_OFFLINE_ACCOUNT_HIS = "update ms_offline_account_his  set bal_is_updated = 1 where  card_no=:cardNo";
  private static final String QUERY_IMPREST_SUBJECT_BY_SERVICE_CODE = "select subject_code From ms_service_imprest_subject where service_no=:serviceNo and is_valid=1";
  
  public List<AccountingRuleVO> listAllAccountingRule()
  {
    return queryForList("select * from ms_accounting_rule", null, AccountingRuleVO.class);
  }
  
  public List<SubAccountingRuleVO> querySubAccountRule(SubAccountingRuleVO paramVO)
  {
    return queryForList("select * from ms_sub_accounting_rule where sub_rule_seq = :subRuleSeq or rule_seq = :ruleSeq ;", paramVO, SubAccountingRuleVO.class);
  }
  
  public int insertAccountingRule(AccountingRuleVO accountingRuleVO)
  {
    accountingRuleVO.setRuleSeq(querySequence());
    return save("insert into ms_accounting_rule(rule_seq,service_no,sub_service_no,subject_code,sub_subject_code,loan_flag,created_by,created_time,updated_by,updated_time,is_valid,balance_flag)  values(:ruleSeq,:serviceNo,:subServiceNo,:subjectCode,:subSubjectCode,:loanFlag,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid,:balanceFlag )", accountingRuleVO);
  }
  
  public int updateAccountingRule(AccountingRuleVO accountingRuleVO)
  {
    return save("update  ms_accounting_rule set service_no = :serviceNo,sub_service_no = :subServiceNo,subject_code = :subjectCode,sub_subject_code = :subSubjectCode,loan_flag = :loanFlag,created_by = :createdBy,created_time = :createdTime,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid,balance_flag = :balanceFlag  where rule_seq = :ruleSeq ", accountingRuleVO);
  }
  
  public int insertSubAccountRule(SubAccountingRuleVO subAccountingRuleVO)
  {
    subAccountingRuleVO.setSubRuleSeq(querySequence());
    return save("insert into ms_sub_accounting_rule(sub_rule_seq,rule_seq,subject_code,sub_subject_code,loan_flag,created_by,created_time,updated_by,updated_time,is_valid,balance_flag)  values(:subRuleSeq,:ruleSeq,:subjectCode,:subSubjectCode,:loanFlag,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid,:balanceFlag )", subAccountingRuleVO);
  }
  
  public int updateSubAccountingRule(SubAccountingRuleVO subAccountingRuleVO)
  {
    return save("update  ms_sub_accounting_rule set rule_seq = :ruleSeq,subject_code = :subjectCode,sub_subject_code = :subSubjectCode,loan_flag = :loanFlag,created_by = :createdBy,created_time = :createdTime,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid,balance_flag = :balanceFlag where sub_rule_seq = :subRuleSeq", subAccountingRuleVO);
  }
  
  public AccountingRuleVO queryRecieverAccountingRule(AccountingRuleVO param)
    throws Exception
  {
    try
    {
      return (AccountingRuleVO)queryForObject("select  service_no as  serviceNo,sub_service_no as subServiceNo , sub_subject_code as  subSubjectCode, updated_time as  updatedTime, updated_by as  updatedBy, is_valid as  isValid, balance_flag as  balanceFlag,  created_time as  createdTime,rule_seq as ruleSeq,loan_flag as loanFlag,  created_by as  createdBy, subject_code as  subjectCode  from ms_accounting_rule  where is_valid=1 and  service_no=:serviceNo and  sub_service_no=:subServiceNo ", param, AccountingRuleVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      return null;
    }
    catch (Exception e)
    {
      LogUtil.error("根据服务编号和子服务编号查记账规则异常,参数AccountingRuleVO为：" + param, e);
      throw e;
    }
  }
  
  public List<TranInfoVO> queryTranInfoWaitForAccounting()
  {
    try
    {
      return queryForList("select receiver_balance as  receiverBalance,  trade_no as   tradeNo, service_no as serviceNo,order_type as orderType, abstract_code as  abstractCode, thd_end_time as  thdEndTime, payment_amount as  paymentAmount, receiver_name as  receiverName,receiver_type as receiverType, accounting_date as  accountingDate, plat_time as  platTime, check_account_date as  checkAccountDate, pay_state_code as  payStateCode, payer_type as  payerType, thd_pay_seq as  thdPaySeq,batch_no as batchNo, channel_code as  channelCode, is_divided as  isDivided, receiver as  receiver, response_code as  responseCode, payer as  payer, original_payer as  originalPayer, \toriginal_payer_type as originalPayerType,app_id as  appId,check_account_sate as checkAccountSate, reason as  reason, original_payer_name as  originalPayerName,thd_pay_amount as thdPayAmount, tran_time as  tranTime, cancle_status as  cancleStatus,payer_balance as payerBalance, adjust_trade_no as  adjustTradeNo, account_state as  accountState, has_adjust as  hasAdjust, pay_type_code as  payTypeCode, payer_name as  payerName, thd_poundage as  thdPoundage, order_no as  orderNo, card_type as cardType,card_no as cardNo  from ms_tran_info where account_state=0 order by trade_no desc limit 20 ", null, TranInfoVO.class);
    }
    catch (Exception e)
    {
      LogUtil.error("查询所有待记账交易异常。", e);
    }
    return new ArrayList();
  }
  
  public List<TranSubVO> queryTranSubList(TranInfoVO tranInfoVO)
  {
    try
    {
      return queryForList("select  check_account_sate as  checkAccountSate, service_no as  serviceNo, sub_trade_no as  subTradeNo, receiver as  receiver, receiver_type as receiverType, cancle_status as  cancleStatus, payer as  payer,payer_type as payerType,  receiver_bal_type as  receiverBalType,  is_cost as isCost, trade_no as  tradeNo, provider as  provider,receiver_is_self as receiverIsSelf, payment_amount as  paymentAmount, receiver_name as  receiverName   from ms_tran_sub_jrnl  where trade_no=:tradeNo", tranInfoVO, TranSubVO.class);
    }
    catch (Exception e)
    {
      LogUtil.error("查询指定交易下交易明细", e);
    }
    return new ArrayList();
  }
  
  public int[] updateAccountStateForTranInfo(List<TranInfoVO> tranInfoList)
    throws Exception
  {
    try
    {
      return batchSave("update ms_tran_info set account_state=:accountState , start_account_time =:startAccountTime where trade_no=:tradeNo", tranInfoList);
    }
    catch (Exception e)
    {
      LogUtil.error("更新交易记账状态异常", e);
      throw e;
    }
  }
  
  public PayChannelVO queryPayChannel(PayChannelVO channel)
    throws Exception
  {
    try
    {
      return (PayChannelVO)queryForObject(" select public_key as  publicKey, plat_account as  platAccount, channel_name as  channelName, is_realtime as  isRealtime, remark as  remark, is_valid as  isValid, callback_url as  callbackUrl,  channel_code as   channelCode,   sign_type as    signType from ms_pay_channel where channel_code=:channelCode and is_valid=1", channel, PayChannelVO.class);
    }
    catch (Exception e)
    {
      LogUtil.error("查询支付渠道异常", e);
      throw e;
    }
  }
  
  public SubjectVO queryBankAccount(String channelCode)
    throws Exception
  {
    try
    {
      PayChannelVO channel = new PayChannelVO();
      channel.setChannelCode(channelCode);
      return (SubjectVO)queryForObject(" select subject_code as subjectCode,channel_code as channelCode from ms_channel_bankaccount where  channel_code=:channelCode and is_valid=1", channel, SubjectVO.class);
    }
    catch (Exception e)
    {
      LogUtil.error("通过渠道号查询银行下的二级科目异常，渠道号为channelCode=" + channelCode, e);
      throw e;
    }
  }
  
  public MerchantVO queryCostServiceMerchant(String serviceCode)
    throws Exception
  {
    try
    {
      MerchantVO param = new MerchantVO();
      param.setServiceNo(serviceCode);
      return (MerchantVO)queryForObject("SELECT mm.merchant_code as merchantCode,mm.bal_type as balType from ms_cost_service_merchant mc,ms_merchant mm where mc.merchant_code = mm.merchant_code and mm.is_valid = 1 and mc.is_valid=1 and mc.service_no=:serviceNo", param, MerchantVO.class);
    }
    catch (Exception e)
    {
      LogUtil.error("查找代收成本服务的虚拟运营商编码异常,参数为：serviceCode=" + serviceCode, e);
      throw e;
    }
  }
  
  public AccountingRuleVO queryAssetsAccountRuleByServiceNo(String serviceNo, Integer subjectType, Integer cprtBalType)
    throws Exception
  {
    try
    {
      AccountingRuleVO param = new AccountingRuleVO();
      param.setServiceNo(serviceNo);
      param.setSubjectType(subjectType);
      param.setCprtBalType(cprtBalType);
      return (AccountingRuleVO)queryForObject("select a.subject_code as subjectCode,a.sub_subject_code as subSubjectCode,a.loan_flag as loanFlag,service_no as serviceNo from ms_assets_lblts_account_rule a , ms_subject s  where a.sub_subject_code = s.subject_code and  a.is_valid=1 and  s.is_valid=1 and  a.service_no=:serviceNo and s.subject_type=:subjectType and a.cprt_bal_type=:cprtBalType", param, AccountingRuleVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      LogUtil.error("根据主服务号查询应收款或应付款项科目为空，参数为serviceNo：" + serviceNo + ",科目类型为：" + subjectType + "，结算类型cprtBalType为：" + cprtBalType, e);
      return null;
    }
    catch (Exception e)
    {
      LogUtil.error("根据主服务号查询应收款或应付款项科目异常，参数为serviceNo：" + serviceNo + ",科目类型为：" + subjectType + "，结算类型cprtBalType为：" + cprtBalType, e);
      throw e;
    }
  }
  
  public SubAccountingRuleVO querySubAccountingRuleByRuleSeq(Long ruleSeq)
    throws Exception
  {
    try
    {
      AccountingRuleVO param = new AccountingRuleVO();
      param.setRuleSeq(ruleSeq);
      return (SubAccountingRuleVO)queryForObject("select rule_seq as ruleSeq,sub_rule_seq as subRuleSeq ,subject_code as subjectCode,sub_subject_code as subSubjectCode,loan_flag as loanFlag  from ms_sub_accounting_rule where is_valid=1 and rule_seq = :ruleSeq ", param, SubAccountingRuleVO.class);
    }
    catch (Exception e)
    {
      LogUtil.error("querySubAccountingRuleByRuleSeq：：根据收款方的记账科目查询付款方的记账科目异常，参数为ruleSeq：" + ruleSeq, e);
      throw e;
    }
  }
  
  public int[] insertMerchAccountDividend(List<MerchAccountDividendVO> paramList)
    throws Exception
  {
    try
    {
      return batchSave("insert into ms_merch_account_dividend(trade_no,merchant_code,subject_code,sub_subject_code,loan_flag,amount,merchant_code1,remark,accounting_date)  values(:tradeNo,:merchantCode,:subjectCode,:subSubjectCode,:loanFlag,:amount,:merchantCode1,:remark,:accountingDate )", paramList);
    }
    catch (Exception e)
    {
      LogUtil.error("insertMerchAccountDividend::插入运营商科目分录异常，参数为ruleSeq：" + paramList, e);
      throw e;
    }
  }
  
  public int[] insertAccountDetail(List<AccountDetailVO> paramList)
    throws Exception
  {
    try
    {
      return batchSave("insert into ms_account_detail(user_id,order_no,trade_no,merchant_code,abstract_code,service_no,pay_type_code,thd_pay_seq,pay_flag,accounting_date,plat_time,payment_amount,remark)  values(:userId,:orderNo,:tradeNo,:merchantCode,:abstractCode,:serviceNo,:payTypeCode,:thdPaySeq,:payFlag,:accountingDate,:platTime,:paymentAmount,:remark )", paramList);
    }
    catch (Exception e)
    {
      LogUtil.error("insertAccountDetail::插入个人账户明细异常，参数为paramList：" + paramList, e);
      throw e;
    }
  }
  
  public void updateTranInfoEndAccounting(List<TranInfoVO> paramList)
    throws Exception
  {
    try
    {
      batchSave("update ms_tran_info set account_state=:accountState , end_account_time =:endAccountTime where trade_no=:tradeNo", paramList);
    }
    catch (Exception e)
    {
      LogUtil.error("updateTranInfoEndAccounting::更新交易记录记账状态为记账完成异常，参数为paramList：" + paramList, e);
      throw e;
    }
  }
  
  public int updateTranInfoEndAccounting(TranInfoVO tranInfo)
    throws Exception
  {
    try
    {
      return save("update ms_tran_info set account_state=:accountState , end_account_time =:endAccountTime where trade_no=:tradeNo", tranInfo);
    }
    catch (Exception e)
    {
      LogUtil.error("updateTranInfoEndAccounting::更新交易记录记账状态为记账完成异常，参数为paramList：" + tranInfo, e);
      throw e;
    }
  }
  
  public int[] updateMerchantSubjectBalance(List<MerchantSubjectAccVO> paramList)
    throws Exception
  {
    try
    {
      return batchSave("UPDATE ms_merchant_subject_acc set acct_bal = acct_bal + :acctBal  where merchant_code=:merchantCode and sub_subject_code=:subSubjectCode", paramList);
    }
    catch (Exception e)
    {
      LogUtil.error("updateMerchantSubjectBalance::更新运营商账户信息异常，参数为paramList：" + paramList, e);
      throw e;
    }
  }
  
  public List<TranInfoVO> queryTranInfoForMonitor(Date platTime)
    throws Exception
  {
    try
    {
      TranInfoVO param = new TranInfoVO();
      param.setPlatTime(platTime);
      return queryForList("select  trade_no as   tradeNo ,account_state as  accountState, plat_time as platTime  from ms_tran_info where  plat_time >=:platTime and (account_state=0  or account_state = 1 )  ", param, TranInfoVO.class);
    }
    catch (Exception e)
    {
      LogUtil.error("updateMerchantSubjectBalance::更新运营商账户信息异常，参数为platTime：" + platTime, e);
      throw e;
    }
  }
  
  public boolean insertOfflineAccountDetail(OfflineAccountDetailVO param)
    throws Exception
  {
    try
    {
      int n = save("insert into ms_offline_account_detail(card_no,order_no,trade_no,merchant_code,abstract_code,service_no,thd_pay_seq,pay_flag,accounting_date,plat_time,payment_amount,remark)  values(:cardNo,:orderNo,:tradeNo,:merchantCode,:abstractCode,:serviceNo,:thdPaySeq,:payFlag,:accountingDate,:platTime,:paymentAmount,:remark )", param);
      return n == 1;
    }
    catch (Exception e)
    {
      LogUtil.error("insertOfflineAccountDetail::插入离线卡账户明细，参数为：" + param, e);
      throw e;
    }
  }
  
  public int insertOfflineAccount(OfflineAccountVO param)
    throws Exception
  {
    try
    {
      return save("insert into ms_offline_account(card_no,user_id,card_type,card_bin,area_code,check_code,valid_date,init_date,init_oper,state,open_oper,open_date,remark,card_cost,acct_bal,bat_no,last_date)  values(:cardNo,:userId,:cardType,:cardBin,:areaCode,:checkCode,:validDate,:initDate,:initOper,:state,:openOper,:openDate,:remark,:cardCost,:acctBal,:batNo,:lastDate )", param);
    }
    catch (Exception e)
    {
      LogUtil.error("insertOfflineAccount::插入离线卡账户异常，参数为：" + param, e);
      throw e;
    }
  }
  
  public boolean updateEntUserBal(UserBaseVO param)
    throws Exception
  {
    try
    {
      int n = save("update ms_user_base set  balance=balance+:balance where user_id =:userId", param);
      return n == 1;
    }
    catch (Exception e)
    {
      LogUtil.error("updateEntUserBal:更新企业客户的余额异常，参数为：" + param, e);
      throw e;
    }
  }
  
  public List<UserBaseVO> queryUserByMerchCode(String merchCode)
    throws Exception
  {
    try
    {
      if (StringUtils.isBlank(merchCode)) {
        return null;
      }
      UserBaseVO user = new UserBaseVO();
      user.setMerchantCode(merchCode);
      return queryForList("select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid  from ms_user_base as t  where t.enterprise_code = :merchantCode and t.is_valid = 1 ", user, UserBaseVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      return null;
    }
    catch (Exception e)
    {
      LogUtil.error("queryUserByMerchCode:通过运营商编码查询用户异常，参数为merchCode：" + merchCode, e);
      throw e;
    }
  }
  
  public OutOfflineCardMerchant queryMerchantByCardType(String outOfflineCardType)
    throws Exception
  {
    try
    {
      OutOfflineCardMerchant o = new OutOfflineCardMerchant();
      o.setCardType(outOfflineCardType);
      return (OutOfflineCardMerchant)queryForObject("select  oo.card_type as cardType ,oo.merchant_code as merchantCode ,mm.short_name as shortName  from  ms_out_offline_card_merchant oo,ms_merchant mm  where oo.merchant_code = mm.merchant_code and oo.card_type=:cardType", o, OutOfflineCardMerchant.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      return null;
    }
    catch (Exception e)
    {
      LogUtil.error("通过外部离线卡类型查询对应运营商异常，外部卡类型为outOfflineCardType：" + outOfflineCardType, e);
      throw e;
    }
  }
  
  public OfflineAccountVO queryHisOfflineCardByNo(String cardNo)
    throws Exception
  {
    try
    {
      OfflineAccountVO param = new OfflineAccountVO();
      param.setCardNo(cardNo);
      return (OfflineAccountVO)queryForObject("select   a.card_no,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.card_cost,a.acct_bal,a.init_date,a.init_oper,a.state,a.open_oper,a.open_date,a.valid_date,a.last_date,a.remark ,a.bal_is_updated as balIsUpdated  from  ms_offline_account_his a  where  a.card_no=:cardNo", param, OfflineAccountVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      return null;
    }
    catch (Exception e)
    {
      LogUtil.error("通过卡号查询历史现金卡异常，参数为cardNo：" + cardNo, e);
      throw e;
    }
  }
  
  public int updateOfflineCardBalUpdatedByNo(String cardNo)
  {
    try
    {
      OfflineAccountVO param = new OfflineAccountVO();
      param.setCardNo(cardNo);
      return save("update ms_offline_account_his  set bal_is_updated = 1 where  card_no=:cardNo", param);
    }
    catch (Exception e)
    {
      LogUtil.error("迁移的离线卡余额状态更新异常，参数为cardNo：" + cardNo, e);
    }
    return 0;
  }
  
  public String queryImprestSubjectByServiceCode(String serviceCode)
  {
    try
    {
      MapSqlParameterSource parameters = new MapSqlParameterSource();
      parameters.addValue("serviceNo", serviceCode);
      List<String> imprestSubjectList = querySingleColumnForList("select subject_code From ms_service_imprest_subject where service_no=:serviceNo and is_valid=1", parameters, String.class);
      if ((imprestSubjectList == null) || (imprestSubjectList.size() != 1)) {
        throw new BusinessException("通过服务编码查询对应预收款科目编码为空，参数serviceCode：=" + serviceCode);
      }
      return (String)imprestSubjectList.get(0);
    }
    catch (Exception e)
    {
      LogUtil.error("通过服务编码查询对应预收款科目编码异常，参数serviceCode：=" + serviceCode, e);
      throw e;
    }
  }
}
